﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Xml.Linq;
using DataAccess.SQL;
using System.Collections.Generic;
using DataAccess.Oracle;
using System.Data.SqlClient;
using CommonLib;
using Framework;
using Entities;
using Entities.System;

namespace BLL.System
{
    /// <summary>
    /// Summary description for Security
    /// </summary>
    public class UserBO : BusinessBase
    {
        /// <summary>验证密码
        /// 
        /// </summary>
        /// <param name="sUserName"></param>
        /// <param name="sPwd"></param>
        /// <returns></returns>
        public bool CheckPwd(string UserID, string Pwd)
        {
            string strSql = @"select u.*
            FROM SBRM_USER u WHERE UPPER(u.USER_ID) = UPPER(@USER_ID) AND u.ACTIVE = 'Y'";
            User user = SqlHelper.ExecuteEntity<User>(strSql, CommandType.Text,
               new SqlParameter[]{ new SqlParameter(){ ParameterName ="@USER_ID" , Value=UserID }      
               });
            if (user == null) return false;
            if (user.PASSWORD != CommonLib.EncryptionLib.GetEncryptor(Pwd, true)) return false;
            else
            {
                HttpContext.Current.Session["user"] = user;
                return true;
            }
        }

        /// <summary>验证域帐户用户是否存在
        /// 
        /// </summary>
        /// <param name="uId"></param>
        /// <returns></returns>
        public bool IsAccountExist(string uAd)
        {
            User user = SqlHelper.ExecuteEntity<User>(@" SELECT u.*
                        FROM SBRM_USER_AD AD
                        INNER JOIN SBRM_USER U ON AD.USER_ID = U.USER_ID
                        WHERE UPPER(AD.USER_AD) = UPPER(@USER_AD) AND u.ACTIVE = 'Y' AND AD.ACTIVE = 'Y'", CommandType.Text,
               new SqlParameter[]{
                      new SqlParameter(){ ParameterName ="@USER_AD" , Value=uAd       }      
               });

            if (user == null) return false;
            else
            {
                HttpContext.Current.Session["user"] = user;
                return true;
            }
        }

        /// <summary>修改密码
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public static string editPwd(HttpContext context)
        {
            UserPwd user = JsonHelper.FromJson<UserPwd>(context.Request.Form["data"]); ;
            try
            {

                User u = (User)HttpContext.Current.Session["user"];
                string newPwd = CommonLib.EncryptionLib.GetEncryptor(user.PasswordNew, true);
                if (CommonLib.EncryptionLib.GetEncryptor(user.Password, true) != u.PASSWORD) throw new Exception("原密码不正确！");

                string sql = string.Format(@" UPDATE SBRM_USER SET Password = @Password WHERE USER_KEY={0}", user.UserKey);

                int result = SqlHelper.ExecuteNonQuery(sql, CommandType.Text, new SqlParameter[]{   
                           new  SqlParameter(){ ParameterName= "@Password", Value=newPwd}
                });
                if (result > 0)
                {
                    u.PASSWORD = newPwd;
                    return (new Status { Success = true, Message = "修改成功！" }).ToJson();
                }
                else return (new Status { Success =  false, Message = "修改失败！" }).ToJson();
            }
            catch (Exception ex)
            {
                var error = new Status { Success = false, Message = ex.Message };
                return error.ToJson();

            }



        }

        /// <summary>取得用户
        /// 
        /// </summary>
        /// <param name="dicLike"></param>
        /// <param name="dicEqual"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageNum"></param>
        /// <returns></returns>
        public  string getUser( HttpContext context,  Dictionary<string, string> dicLike, Dictionary<string, string> dicEqual, int? pageSize, int? pageNum)
        {
            List<User> lst = new List<User>();
            string attachCondition = "";
            if (dicLike.ContainsKey("attachCondition"))
            {
                attachCondition = string.Format("and ({0})", dicLike["attachCondition"]);
                dicLike.Remove("attachCondition");
            }
            var condition = getCondition(dicLike, dicEqual);
            condition += attachCondition;
            if (!string.IsNullOrEmpty(context.Request["isAdmin"]))
            {
                if (Convert.ToBoolean(context.Request["isAdmin"]) == false)
                {
                    condition += " and USER_ID <> 'Administrator' ";
                }
            }
            string strSql = string.Empty;

            if (pageSize.HasValue && pageNum.HasValue)
            {
                int from = pageSize.Value * (pageNum.Value - 1);
                int to = pageSize.Value * pageNum.Value;
                string strTableName = string.Format(@"(SELECT  u.*
FROM SBRM_USER u  
LEFT JOIN SBRM_ORGAN o on u.ORGAN_KEY=o.ORGAN_KEY where 1 = 1 {0} ) U", condition);
                strSql = Common.EasyUIHelper.GetSqlOfPageData(pageNum, pageSize, strTableName, "USER_ID", "", "");
            }
            else
            {

                strSql = string.Format(@"  SELECT  u.*
FROM SBRM_USER u  
LEFT JOIN SBRM_ORGAN o on u.ORGAN_KEY = o.ORGAN_KEY where 1 = 1 {0}                                                   
ORDER BY U.USER_ID  ", condition);
            }

            try
            {
                lst = SqlHelper.ExecuteList<User>(strSql, CommandType.Text);
                //解密
                lst.ForEach(user => user.PASSWORD = CommonLib.EncryptionLib.GetDecryptor(user.PASSWORD, true));


                //总条数
                strSql = string.Format(@"select count(*)  as rowsCount
FROM  SBRM_USER u
LEFT JOIN  SBRM_ORGAN o on u.ORGAN_KEY = o.ORGAN_KEY where 1 = 1 {0}   ", condition);
                object oCount = SqlHelper.ExecuteScalar(strSql, CommandType.Text);
                int total = Convert.ToInt32(oCount);
                return (new Result<User> { rows = lst, total = total }).ToJson();

            }
            catch (Exception ex)
            {
                var error = new Status { Success = false, Message = ex.Message };
                return error.ToJson();

            }

        }

        public List<Entities.System.TEUser> getTEUser(Entities.System.User _User)
        {
            string strSQL = string.Empty;
            if (!_User.USER_ID.Equals("Administrator"))
            {
                strSQL = string.Format(@"
                    USER_KEY IN (
                        SELECT DISTINCT(USER_KEY)
                        FROM SBRM_USER_GROUP
                        WHERE GROUP_KEY IN (
                            SELECT GROUP_KEY
                            FROM SBRM_USER_GROUP
                            WHERE USER_KEY={0})
                    ) AND",
                    _User.USER_KEY);
            }
            strSQL = string.Format(@"
                SELECT USER_KEY, USER_ID, USER_NAME
                FROM SBRM_USER
                WHERE {0} USER_ID<>'Administrator'
                ORDER BY USER_ID",
                strSQL);

            try
            {
                List<Entities.System.TEUser> lst = SqlHelper.ExecuteList<Entities.System.TEUser>(strSQL, CommandType.Text);
                return lst;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>删除用户
        ///  
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public  string delUser(HttpContext context)
        {
            string strSql = string.Empty;
            //删除
            List<string> sqlArr = new List<string>();
            sqlArr.Add(string.Format("delete from SBRM_USER_GROUP Where USER_KEY = '{0}'", context.Request.Form["id"]));
            sqlArr.Add(string.Format("delete from SBRM_USER_GROUP_LOG Where USER_KEY = '{0}'", context.Request.Form["id"]));
            sqlArr.Add(string.Format("delete from  SBRM_USER  where  USER_KEY='{0}'", context.Request.Form["id"]));
            bool result=  SqlHelper.ExecuteSqlTran(sqlArr);
            if (result) return (new Status { Success = true }).ToJson();
            else return (new Status { Success = false, Message = "执行异常!" }).ToJson();
        }

        /// <summary>新增用户
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public  string addUser(HttpContext context)
        {
            string strSql = string.Empty;
            User userAdd = JsonHelper.FromJson<User>(context.Request.Form["data"]);
            //判断是否存在
            object result;
            if (string.IsNullOrEmpty(userAdd.COMPANY_CODE))
            {
                strSql = "select count(*) from SBRM_USER where USER_ID = '{0}' and COMPANY_CODE is null ";
                strSql = string.Format(strSql, userAdd.USER_ID);
                result = SqlHelper.ExecuteScalar(strSql, CommandType.Text);
            }
            else
            {
                strSql = "select count(*) from SBRM_USER where USER_ID = '{0}' and COMPANY_CODE = '{1}'  ";
                strSql = string.Format(strSql, userAdd.USER_ID, userAdd.COMPANY_CODE);
                result = SqlHelper.ExecuteScalar(strSql, CommandType.Text);
            }

            if (result != null && result != DBNull.Value)
            {
                if (Convert.ToInt32(result) > 0) throw new Exception("此公司别已经存在此用户代码！");
            }

            var pwd = CommonLib.EncryptionLib.GetEncryptor(userAdd.PASSWORD, true);
            //新增
            bool result2 = true;
            User user = context.Session["user"] as User;
            strSql = @"insert into SBRM_USER
(USER_ID,COMPANY_CODE,USER_NAME,active,PASSWORD,USER_MAIL,TELEPHONE,SIGN_PATH,SELF_FLAG,
DEP_DESC, UPDATE_USER_KEY,UPDATE_DATE) 
values 
(@USER_ID, @COMPANY_CODE, @USER_NAME, @active, @PASSWORD, @USER_MAIL, @TELEPHONE, @SIGN_PATH,@SELF_FLAG,
@DEP_DESC, @UPDATE_USER_KEY, GETDATE())
{0} ";
            strSql = string.Format(strSql, Common.Common.ReturnIdentitySql);

            using (SqlConnection connection = SqlHelper.Con)
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                SqlTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    //插入
                    cmd.CommandText = strSql;
                    var parms = new SqlParameter[] {
                    new SqlParameter{ ParameterName="@USER_NAME" , Value=userAdd.USER_NAME  },
                    new SqlParameter{ ParameterName="@USER_ID" , Value= userAdd.USER_ID  },
                    new SqlParameter{ ParameterName="@COMPANY_CODE" , Value=userAdd.COMPANY_CODE },
                    new SqlParameter{ ParameterName="@PASSWORD" , Value= pwd },
                    new SqlParameter{ ParameterName="@USER_MAIL" , Value=userAdd.USER_MAIL },
                    new SqlParameter{ ParameterName="@TELEPHONE" , Value=userAdd.TELEPHONE },
                    new SqlParameter{ ParameterName="@SIGN_PATH" , Value=userAdd.SIGN_PATH },
                    new SqlParameter{ ParameterName="@ACTIVE" , Value=userAdd.ACTIVE  },
                    new SqlParameter{ ParameterName="@DEP_DESC" , Value=userAdd.DEP_DESC },
                    new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                    new SqlParameter{ ParameterName="@SELF_FLAG" , Value= userAdd.SELF_FLAG  } };
                    cmd.Parameters.AddRange(parms);
                    //cmd.ExecuteNonQuery();
                    object newID = cmd.ExecuteScalar();
                    userAdd.USER_KEY = Convert.ToInt32(newID);
                    //插入日志
                    //insertLog(user, group, "ADD", tx);
                    tx.Commit();
                }
                catch (Exception E)
                {
                    result2 = false;
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
            if (result2) return (new Status { Success = true }).ToJson();
            else return (new Status { Success = false, Message = "执行异常!" }).ToJson();

        }

        /// <summary>修改用户信息
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public  string editUser(HttpContext context)
        {
            string strSql = string.Empty;
            User userEdit = JsonHelper.FromJson<User>(context.Request.Form["data"]);
            string attachWhere = string.Empty;
            //判断是否存在
            if (userEdit.USER_KEY != 0) attachWhere += string.Format(" and USER_KEY <> {0}", userEdit.USER_KEY);
            object result ;
            if (string.IsNullOrEmpty(userEdit.COMPANY_CODE))
            {
                strSql = "select count(*) from SBRM_USER where  USER_ID = '{0}' and COMPANY_CODE is null {1}";
                strSql = string.Format(strSql, userEdit.USER_ID, attachWhere);
                result = SqlHelper.ExecuteScalar(strSql, CommandType.Text);
            }
            else
            {
                strSql = "select count(*) from SBRM_USER where USER_ID = '{0}' and COMPANY_CODE = '{1}' {2}  ";
                strSql = string.Format(strSql, userEdit.USER_ID, userEdit.COMPANY_CODE, attachWhere);
                result = SqlHelper.ExecuteScalar(strSql, CommandType.Text);
            }

            if (result != null && result != DBNull.Value)
            {
                if (Convert.ToInt32(result) > 0) throw new Exception("此公司别已经存在此用户代码！");

            }

            int result2 = 0;
            var pwd = CommonLib.EncryptionLib.GetEncryptor(userEdit.PASSWORD, true);
            //编辑
            strSql = @"update SBRM_USER 
set USER_ID=@USER_ID,USER_NAME=@USER_NAME,active=@active,PASSWORD=@PASSWORD,USER_MAIL=@USER_MAIL,
COMPANY_CODE=@COMPANY_CODE,DEP_DESC=@DEP_DESC,TELEPHONE=@TELEPHONE,SIGN_PATH=@SIGN_PATH,
UPDATE_USER_KEY=@UPDATE_USER_KEY,UPDATE_DATE=GETDATE(),SELF_FLAG=@SELF_FLAG
where  USER_KEY = @USER_KEY ";
            User user = context.Session["user"] as User;
            result2 = SqlHelper.ExecuteNonQuery(strSql, CommandType.Text, new SqlParameter[] { 
                    new SqlParameter{ ParameterName="@USER_KEY" , Value=userEdit.USER_KEY  },
                    new SqlParameter{ ParameterName="@USER_NAME" , Value=userEdit.USER_NAME  },
                    new SqlParameter{ ParameterName="@USER_ID" , Value= userEdit.USER_ID  },
                    new SqlParameter{ ParameterName="@COMPANY_CODE" , Value=userEdit.COMPANY_CODE },
                    new SqlParameter{ ParameterName="@PASSWORD" , Value= pwd },
                    new SqlParameter{ ParameterName="@USER_MAIL" , Value=userEdit.USER_MAIL },
                    new SqlParameter{ ParameterName="@TELEPHONE" , Value=userEdit.TELEPHONE },
                    new SqlParameter{ ParameterName="@SIGN_PATH" , Value=userEdit.SIGN_PATH },
                    new SqlParameter{ ParameterName="@ACTIVE" , Value=userEdit.ACTIVE  },
                    new SqlParameter{ ParameterName="@DEP_DESC" , Value=userEdit.DEP_DESC },
                    new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                    new SqlParameter{ ParameterName="@SELF_FLAG" , Value= userEdit.SELF_FLAG  } });

            if (result2 > 0) return (new Status { Success = true }).ToJson();
            else return (new Status { Success = false, Message = "执行异常!" }).ToJson();

        }

        /// <summary>记录登录日志
        /// 
        /// </summary>
        /// <param name="context"></param>
        public void addLoginLog(HttpContext context)
        {

            string sql = string.Empty;
            LoginLog log = JsonHelper.FromJson<LoginLog>(context.Request.Form["data"]);

            User user = context.Session["user"] as User;
            sql = @"insert into SBRM_LOGIN_LOG
(FUNCTION_KEY, FUNCTION_NAME , LOGIN_USER_KEY, LOGIN_USER_ID, LOGIN_USER_NAME,
LOGIN_IP, LOGIN_DATE) 
values 
(@FUNCTION_KEY, @FUNCTION_NAME, @LOGIN_USER_KEY, @LOGIN_USER_ID, @LOGIN_USER_NAME,
@LOGIN_IP, GETDATE())";
            try
            {
                //插入
                int result = SqlHelper.ExecuteNonQuery(sql, CommandType.Text, new SqlParameter[] {
                        new SqlParameter{ ParameterName="@LOGIN_USER_KEY" , Value=user.USER_KEY   },
                        new SqlParameter{ ParameterName="@LOGIN_USER_NAME" , Value=user.USER_NAME },
                        new SqlParameter{ ParameterName="@LOGIN_USER_ID" , Value= user.USER_ID  },
                        new SqlParameter{ ParameterName="@LOGIN_IP" , Value=context.Request.ServerVariables["REMOTE_ADDR"] },
                        new SqlParameter{ ParameterName="@FUNCTION_KEY" , Value= log.FUNCTION_KEY },
                        new SqlParameter{ ParameterName="@FUNCTION_NAME" , Value=log.FUNCTION_NAME  } }
                    );

            }
            catch (Exception E)
            {

            }
            finally
            {

            }

        }
    }
}

